using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 部门员工进出查询工具 - 支持分表查询
/// 覆盖场景：P-005 部门员工进出, P-008 员工信息查询（跨月）, P-009 各部门进校统计
/// </summary>
public class GetDepartmentEmployeeTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetDepartmentEmployeeTool> _logger;

    public string Name => "get_department_employee";
    public string Description => "查询部门员工信息和进出记录，支持跨月分表查询。用于回答'某部门员工今天进出记录'、'查询员工信息'等问题。";

    public GetDepartmentEmployeeTool(
        IDatabaseExecutor executor,
        ILogger<GetDepartmentEmployeeTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            string? department = parameters.ContainsKey("department") ? parameters["department"]?.ToString() : null;
            string? employeeName = parameters.ContainsKey("employeeName") ? parameters["employeeName"]?.ToString() : null;
            string? queryDate = parameters.ContainsKey("queryDate") ? parameters["queryDate"]?.ToString() : null;
            string queryType = parameters.ContainsKey("queryType") ? parameters["queryType"]?.ToString() ?? "access" : "access";
            int months = parameters.ContainsKey("months") ? Convert.ToInt32(parameters["months"]) : 1;

            if (string.IsNullOrEmpty(queryDate))
            {
                queryDate = DateTime.Today.ToString("yyyy-MM-dd");
            }

            var sql = queryType switch
            {
                "info" => BuildEmployeeInfoQuery(employeeName, queryDate, months),
                _ => BuildAccessRecordsQuery(department, queryDate)
            };

            _logger.LogInformation("Executing department employee query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "部门员工查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["queryType"] = queryType
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing department employee query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    /// <summary>
    /// 构建部门员工进出记录查询
    /// </summary>
    private string BuildAccessRecordsQuery(string? department, string queryDate)
    {
        var employeeTable = GetEmployeeTableName(DateTime.Parse(queryDate));
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    g.XGH AS employee_number,");
        sb.AppendLine("    g.XM AS name,");
        sb.AppendLine("    e.Department AS department,");
        sb.AppendLine("    DATE_FORMAT(g.SJ, '%Y-%m-%d %H:%i:%s') AS access_time,");
        sb.AppendLine("    CASE g.CRBS");
        sb.AppendLine("        WHEN 1 THEN '进'");
        sb.AppendLine("        WHEN 0 THEN '出'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS direction,");
        sb.AppendLine("    g.ZJMC AS gate_name");
        sb.AppendLine("FROM TblGateInformation g");
        sb.AppendLine($"LEFT JOIN {employeeTable} e ON g.XGH = e.EmployeeNumber");
        sb.AppendLine($"WHERE DATE(g.SJ) = '{queryDate}'");

        if (!string.IsNullOrEmpty(department))
        {
            sb.AppendLine($"    AND e.Department LIKE '%{department}%'");
        }

        sb.AppendLine("    AND e.Department IS NOT NULL");
        sb.AppendLine("ORDER BY g.SJ DESC");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }

    /// <summary>
    /// 构建员工信息查询（支持跨月）
    /// </summary>
    private string BuildEmployeeInfoQuery(string? employeeName, string queryDate, int months)
    {
        var sb = new StringBuilder();
        var baseDate = DateTime.Parse(queryDate);

        if (months == 1)
        {
            // 单月查询
            var table = GetEmployeeTableName(baseDate);
            sb.AppendLine("SELECT");
            sb.AppendLine("    Name AS name,");
            sb.AppendLine("    EmployeeNumber AS employee_number,");
            sb.AppendLine("    Department AS department,");
            sb.AppendLine($"    '{baseDate:yyyy-MM}' AS month");
            sb.AppendLine($"FROM {table}");
            sb.AppendLine("WHERE 1=1");

            if (!string.IsNullOrEmpty(employeeName))
            {
                sb.AppendLine($"    AND Name LIKE '%{employeeName}%'");
            }

            sb.AppendLine("LIMIT 50");
        }
        else
        {
            // 跨月查询 - 使用 UNION ALL
            for (int i = 0; i < months; i++)
            {
                if (i > 0)
                {
                    sb.AppendLine("UNION ALL");
                }

                var monthDate = baseDate.AddMonths(-i);
                var table = GetEmployeeTableName(monthDate);

                sb.AppendLine("SELECT");
                sb.AppendLine("    Name AS name,");
                sb.AppendLine("    EmployeeNumber AS employee_number,");
                sb.AppendLine("    Department AS department,");
                sb.AppendLine($"    '{monthDate:yyyy-MM}' AS month");
                sb.AppendLine($"FROM {table}");
                sb.AppendLine("WHERE 1=1");

                if (!string.IsNullOrEmpty(employeeName))
                {
                    sb.AppendLine($"    AND Name LIKE '%{employeeName}%'");
                }
            }

            sb.AppendLine("ORDER BY month DESC");
            sb.AppendLine("LIMIT 50");
        }

        return sb.ToString();
    }

    /// <summary>
    /// 获取员工表名（按月分表）
    /// </summary>
    private string GetEmployeeTableName(DateTime date)
    {
        return $"TblEmployee_{date:yyyyMM}01";
    }
}
